using System;
using System.Collections.Generic;
using System.Text;
using System.Globalization;
using System.Data.SqlClient;
using System.Data;

namespace CodeGenerator.Classes
{
    public class GeneratorForms
    {
        private string mTableName = "";
        private string connStr = "";

        public static string NameSpace = string.Empty;

        public GeneratorForms(string connectionString, string TableName)
        {
            mTableName = TableName;
            connStr = connectionString;
        }

        public static string PropCaseStartLower(string strText)
        {
            string retVal = PropCase(strText);
            retVal = retVal.Substring(0, 1).ToLower() + retVal.Substring(1, (retVal.Length - 1));
            return retVal;
        }

        public static string PropCase(string strText)
        {
            return new CultureInfo("en").TextInfo.ToTitleCase(strText.ToLower());
        }

        private SqlCommand GetCommand(string sql)
        {
            SqlConnection conn = new SqlConnection(connStr);
            SqlCommand cmd = new SqlCommand(sql, conn);
            conn.Open();
            return cmd;
        }

        private string GetPK(string table)
        {

            string pk = "";
            DataTable pkTable = new DataTable();
            string sql = @"SELECT KCU.COLUMN_NAME 
        FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE KCU
        JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS TC
        ON KCU.CONSTRAINT_NAME=TC.CONSTRAINT_NAME
        WHERE TC.CONSTRAINT_TYPE='PRIMARY KEY'
		AND KCU.TABLE_NAME=@tableName";

            SqlCommand cmd = GetCommand(sql);
            cmd.Parameters.AddWithValue("@tableName", table);
            object result = cmd.ExecuteScalar();
            cmd.Dispose();
            if (result != null)
                pk = result.ToString();

            return pk;
        }

        private string DecodeType(string sqlType)
        {
            sqlType = sqlType.ToLower();
            string sysType = "string";
            switch (sqlType)
            {
                case "bigint":
                    sysType = "long";
                    break;
                case "smallint":
                    sysType = "short";
                    break;
                case "int":
                    sysType = "int";
                    break;
                case "uniqueidentifier":
                    sysType = "Guid";
                    break;
                case "smalldatetime":
                case "datetime":
                case "date":
                    sysType = "DateTime";
                    break;
                case "float":
                    sysType = "double";
                    break;
                case "real":
                case "numeric":
                case "smallmoney":
                case "decimal":
                case "money":
                    sysType = "decimal";
                    break;
                case "tinyint":
                    sysType = "byte";
                    break;
                case "bit":
                    sysType = "bool";
                    break;
                case "image":
                case "binary":
                case "varbinary":
                case "timestamp":
                    sysType = "byte[]";
                    break;
            }
            return sysType;
        }
        private string DecodeTypeForDB(string sqlType)
        {
            switch (sqlType)
            {
                case "varchar":
                    return "VarChar";
                case "nvarchar":
                    return "NVarChar";
                case "int":
                    return "Int";
                case "uniqueidentifier":
                    return "UniqueIdentifier";
                case "datetime":
                    return "DateTime";
                case "bigint":
                    return "BigInt";
                case "binary":
                    return ".Binary";
                case "bit":
                    return "Bit";
                case "char":
                    return "Char";
                case "decimal":
                    return "Decimal";
                case "float":
                    return "Float";
                case "image":
                    return "Image";
                case "money":
                    return "Money";
                case "nchar":
                    return "NChar";
                case "ntext":
                    return "NText";
                case "numeric":
                    return "Decimal";
                case "real":
                    return "Real";
                case "smalldatetime":
                    return "SmallDateTime";
                case "smallint":
                    return "SmallInt";
                case "smallmoney":
                    return "SmallMoney";
                case "sql_variant":
                    return "Variant";
                case "sysname":
                    return "VarChar";
                case "text":
                    return "Text";
                case "timestamp":
                    return "Timestamp";
                case "tinyint":
                    return "TinyInt";
                case "varbinary":
                    return "VarBinary";
                case "xml":
                    return "Xml";
                default:
                    return "VarChar";
            }
        }

        public string GeneratForms()
        {

            StringBuilder sql = new StringBuilder();

            SqlConnection conn = new SqlConnection();
            conn.ConnectionString = connStr;
            string cmdTxt = "select * from  INFORMATION_SCHEMA.TABLES where TABLE_TYPE='BASE TABLE' and TABLE_NAME ='"+mTableName+"'";

            SqlCommand cmd = new SqlCommand();
            cmd.CommandText = cmdTxt;
            cmd.Connection = conn;
            cmd.Dispose();

            SqlDataAdapter oda = new SqlDataAdapter(cmd);
            DataTable dt = new DataTable();
            oda.Fill(dt);

            string TABLE_NAME = string.Empty;
            string tType = string.Empty;
            string pkTable = string.Empty;
            string keyType = string.Empty;
            string keyDbType= string.Empty;

            string collName = string.Empty;
            string destColl = string.Empty;
            string parColl = string.Empty;
            string dbType = string.Empty;
            string isNull = string.Empty;
            string updCollPar= string.Empty;
            string collTemp = string.Empty;
            string parTemp = string.Empty;

            string insColl = string.Empty;
            string insCollPar = string.Empty;
            string paramColl = string.Empty;
            string clearColl = string.Empty;
            string htmlTextBox = string.Empty;


            foreach (DataRow dr in dt.Rows)
            {

                TABLE_NAME = PropCaseStartLower(dr["TABLE_NAME"].ToString()).Replace("_", "");

                pkTable = GetPK(dr["TABLE_NAME"].ToString());

                cmdTxt = "select data_type from INFORMATION_SCHEMA.COLUMNS where table_name = '" + dr["TABLE_NAME"].ToString() + "' and ordinal_position = 1";

                SqlCommand cmdType = new SqlCommand();
                cmdType.CommandText = cmdTxt;
                cmdType.Connection = conn;

                SqlDataAdapter odaColl = new SqlDataAdapter(cmdType);
                DataTable dtColl = new DataTable();
                odaColl.Fill(dtColl);

                keyType = DecodeType(dtColl.Rows[0][0].ToString());
                keyDbType = DecodeTypeForDB(dtColl.Rows[0][0].ToString());

                cmdTxt = @"SELECT 
					TABLE_CATALOG,
					TABLE_SCHEMA, 
					TABLE_NAME, 
					COLUMN_NAME, 
					ORDINAL_POSITION, 
					COLUMN_DEFAULT AS DefaultSetting, 
					IS_NULLABLE,
					DATA_TYPE, 
					CHARACTER_MAXIMUM_LENGTH AS MaxLength, 
					DATETIME_PRECISION AS DatePrecision,
					COLUMNPROPERTY(object_id('[' + TABLE_SCHEMA + '].[' + TABLE_NAME + ']'), COLUMN_NAME, 'IsIdentity') AS IsIdentity,
					COLUMNPROPERTY(object_id('[' + TABLE_SCHEMA + '].[' + TABLE_NAME + ']'), COLUMN_NAME, 'IsComputed') as IsComputed
				FROM  INFORMATION_SCHEMA.COLUMNS
				WHERE TABLE_NAME= '" + dr["TABLE_NAME"].ToString() + "' ORDER BY Ordinal_Position";


                SqlCommand cmdColl = new SqlCommand();
                cmdColl.CommandText = cmdTxt;
                cmdColl.Connection = conn;
                cmdColl.Dispose();

                odaColl = new SqlDataAdapter(cmdColl);
                dtColl = new DataTable();
                odaColl.Fill(dtColl);

                collName = string.Empty;
                destColl = string.Empty;
                parColl = string.Empty;
                dbType = string.Empty;
                isNull = string.Empty;
                updCollPar = string.Empty;
                collTemp = string.Empty;
                parTemp = string.Empty;

                insColl = string.Empty;
                insCollPar = string.Empty;
                paramColl = string.Empty;
                clearColl = string.Empty;
                htmlTextBox = string.Empty;

                foreach (DataRow drColl in dtColl.Rows)
                {
                    collTemp = drColl["COLUMN_NAME"].ToString();
                    parTemp = PropCaseStartLower(drColl["COLUMN_NAME"].ToString()).Replace("_", "");

                    destColl = destColl + ", " + collTemp;
                    parColl = parColl + ", @" + parTemp;

                    int isIdentity = (drColl["IsIdentity"] == null ? 0 : Convert.ToInt32(drColl["IsIdentity"]));

                    if (!(collTemp.ToLower().Contains("create")) && !(collTemp.ToLower().Contains("password")) && isIdentity != 1)
                    {
                        updCollPar = updCollPar + ", " + collTemp + " = @" + parTemp + "\n";
                    }

                    if (isIdentity == 0 && !(collTemp.ToLower().Contains("modified")) && !(collTemp.ToLower().Contains("created")))
                    {
                        insColl = insColl + "\n" + "\t\t\t var " + PropCaseStartLower(collTemp).Replace("_", "") + " = encodeURIComponent($('#<%=txt" + PropCaseStartLower(collTemp).Replace("_", "") + ".ClientID %>').val());";
                        insCollPar = insCollPar + ", @" + parTemp + "\n";

                        paramColl = paramColl + "&'" + PropCaseStartLower(collTemp).Replace("_", "") + "='" + "+" + PropCaseStartLower(collTemp).Replace("_", "") + "+";
                        // sql.AppendLine("var title = encodeURIComponent($('#<%=txtTitle.ClientID %>').val());");

                        clearColl = clearColl + "\n" + "\t\t\t$('#<%=txt" + PropCaseStartLower(collTemp).Replace("_", "") + ".ClientID %>').val('');";

                       

                        htmlTextBox = htmlTextBox + @" <tr>
                                    <td>"+
                                       PropCaseStartLower(collTemp).Replace("_", "") +
                                    @"</td>
                                    <td> 
                                        :
                                    </td>
                                    <td> 
                                        <asp:TextBox ID=" + "\"" + "txt" + PropCaseStartLower(collTemp).Replace("_", "") + "\"" + " runat=" + "\"" + "server" + "\"" + " Width=" + "\"" + "100" + "\"" + "  CssClass=" + "\"" + "validate[required]" + "\"" + "></asp:TextBox>" +
                                    @"</td>
                                </tr>";
                       
                    }

                }

                //insColl = insColl.TrimStart(',').Trim();
                insCollPar = insCollPar.TrimStart(',').Trim();

                paramColl = paramColl.TrimEnd('+').Trim();
                //clearColl = clearColl.TrimStart(',').Trim();

                updCollPar = updCollPar.TrimStart(',').Trim();
                destColl = destColl.TrimStart(',').Trim();
                parColl = parColl.TrimStart(',').Trim();

                sql.AppendLine("<%@ Page Title=" + "\"" + "Menu Form" + "\"" + " Language=" + "\"" + "C#" + "\"" + " MasterPageFile=" + "\"" + "~/Form.Master" + "\"" + " AutoEventWireup=" + "\"" + "true" + "\"" + " CodeFile=" + "\"" + "sMenu.aspx.cs" + "\"" + " Inherits=" + "\"" + "Form_Administration_sMenu" + "\"" + " %>");
                sql.AppendLine("\n");
                sql.AppendLine("<asp:Content ID=" + "\"" + "Content1" + "\"" + " ContentPlaceHolderID=" + "\"" + "head" + "\"" + " runat=" + "\"" + "Server" + "\"" + ">");
                sql.AppendLine("\n");
                sql.AppendLine("<script type=" + "\"" + "text/javascript" + "\"" + ">");
                sql.AppendLine("\t\tjQuery(document).ready(function () {");
                sql.AppendLine("\t\t// binds form submission and fields to the validation engine");
                sql.AppendLine("\t\t\tjQuery(" + "\"" + ".form1" + "\"" + ").validationEngine();");
                sql.AppendLine("\t\t\tif ($(" + "\"" + ".form1" + "\"" + ").validationEngine('validate')) { }");
                sql.AppendLine("\t\t\telse jQuery(" + "\"" + ".form1" + "\"" + ").submit();");
                sql.AppendLine("\t\t});");
                sql.AppendLine("</script>");
                sql.AppendLine(" <!--END-VALIDATION-->");
                sql.AppendLine("\n");
                sql.AppendLine("<script type=" + "\"" + "text/javascript" + "\"" + " language=" + "\"" + "javascript" + "\"" + ">");
                sql.AppendLine("\t$(function () {");
                sql.AppendLine("\t\t$('#save').click(function () {");
                sql.AppendLine("\t\t\tif ($(" + "\"" + ".form1" + "\"" + ").validationEngine('validate')) {");
                sql.AppendLine(insColl);
                sql.AppendLine("\n");
                sql.AppendLine("\t\t\tvar param =" + paramColl + ";");
                sql.AppendLine("\n");
                sql.AppendLine("\t\t\tif (id == 0)");
                sql.AppendLine("\t\t\t\tCallAjax(" + "\"" + "../../Ajaxhandler/Administration/" + PropCaseStartLower(mTableName) + ".ashx" + "\"" + ", " + "\"" + "?method=insert&" + "\"" + " + param);");
                sql.AppendLine("\t\t\telse");
                sql.AppendLine("\t\t\t\tCallAjax(" + "\"" + "../../Ajaxhandler/Administration/" + PropCaseStartLower(mTableName) + ".ashx" + "\"" + ", " + "\"" + "?method=update&" + "\"" + " + param + " + "\"" + "&id=" + "\"" + "+ id);");
                sql.AppendLine("\t\t\tparent.$("+"\""+"#dialog"+"\""+").dialog("+"\""+"close"+"\""+");");
                sql.AppendLine("\n");
                sql.AppendLine("\t\t\tsetTimeout("+"\""+"parent.$('#table').trigger('reloadGrid');"+"\""+", 3000);");
                sql.AppendLine("\t\t\t}");
                sql.AppendLine("\t\treturn false;");
                sql.AppendLine("\t\t});");
                sql.AppendLine("\t});");
                sql.AppendLine("\n");
                sql.AppendLine("function ClearText() {");
                sql.AppendLine(clearColl);
                sql.AppendLine("}");
                sql.AppendLine("</script>");
                sql.AppendLine("</asp:Content>");
                sql.AppendLine("\n");
                sql.AppendLine("<asp:Content ID="+"\""+"Content2"+"\""+" ContentPlaceHolderID="+"\""+"ContentPlaceHolder1"+"\""+" runat="+"\""+"Server"+"\""+">");
                sql.AppendLine("<table border="+"\""+"0"+"\""+">");
                sql.AppendLine(htmlTextBox);
                sql.AppendLine("</table>");

                sql.AppendLine("<asp:HiddenField ID="+"\""+"hfID"+"\""+" runat="+"\""+"server"+"\""+" />");
                sql.AppendLine("<div class="+"\""+"butForm"+"\""+">");
                sql.AppendLine("<a href="+"\""+"javascript:void(0);"+"\""+" id="+"\""+"save"+"\""+" class="+"\""+"dialog_link ui-state-default ui-corner-all"+"\"");
                sql.AppendLine("title="+"\""+"Save"+"\""+"><span class="+"\""+"ui-icon ui-icon-disk"+"\""+"></span>Save</a> <a href="+"\""+"javascript:void(0);"+"\"");
                sql.AppendLine("id=" + "\"" + "close" + "\"" + " class=" + "\"" + "dialog_link ui-state-default ui-corner-all" + "\"" + " title=" + "\"" + "Close" + "\"" + " onclick=" + "\"" + "parent.$('#dialog').dialog('close');" + "\"" + ">");
                sql.AppendLine("<span class="+"\""+"ui-icon ui-icon-close"+"\""+"></span>Cancel</a>");
                sql.AppendLine("</div>");
                sql.AppendLine("</asp:Content>");

            }




//<asp:Content ID="Content2" ContentPlaceHolderID="ContentPlaceHolder1" runat="Server">
//    <table border="0">
//        <tr>
//            <td>
//                Kode Sistem
//            </td>
//            <td>
//                :
//            </td>
//            <td>
//                <asp:TextBox ID="txtCode" runat="server" Width="20" MaxLength="2" CssClass="validate[required]"></asp:TextBox>
//            </td>
//        </tr>
//        <tr>
//            <td>
//                Deskripsi Sistem
//            </td>
//            <td>
//                :
//            </td>
//            <td>
//                <asp:TextBox ID="txtDesc" runat="server" Width="300" CssClass="validate[required]"></asp:TextBox>
//            </td>
//        </tr>
//    </table>
//    <asp:HiddenField ID="hfID" runat="server" />
//    <div class="butForm">
//        <a href="javascript:void(0);" id="save" class="dialog_link ui-state-default ui-corner-all"
//            title="Save"><span class="ui-icon ui-icon-disk"></span>Save</a> <a href="javascript:void(0);"
//                id="close" class="dialog_link ui-state-default ui-corner-all" title="Close" onclick="parent.$('#dialog').dialog('close');">
//                <span class="ui-icon ui-icon-close"></span>Cancel</a>
//    </div>
//</asp:Content>


            return sql.ToString();
        }

        public string GeneratFormCode()
        {

            StringBuilder sql = new StringBuilder();

            SqlConnection conn = new SqlConnection();
            conn.ConnectionString = connStr;
            string cmdTxt = "select * from  INFORMATION_SCHEMA.TABLES where TABLE_TYPE='BASE TABLE' and TABLE_NAME ='"+mTableName+"'";

            SqlCommand cmd = new SqlCommand();
            cmd.CommandText = cmdTxt;
            cmd.Connection = conn;
            cmd.Dispose();

            SqlDataAdapter oda = new SqlDataAdapter(cmd);
            DataTable dt = new DataTable();
            oda.Fill(dt);

            string TABLE_NAME = string.Empty;
            string tType = string.Empty;
            string pkTable = string.Empty;
            string keyType = string.Empty;
            string keyDbType= string.Empty;

            string collName = string.Empty;
            string destColl = string.Empty;
            string parColl = string.Empty;
            string dbType = string.Empty;
            string isNull = string.Empty;
            string updCollPar= string.Empty;
            string collTemp = string.Empty;
            string parTemp = string.Empty;

            string insColl = string.Empty;
            string insCollPar = string.Empty;
            string paramColl = string.Empty;
            string clearColl = string.Empty;
            string htmlTextBox = string.Empty;


            foreach (DataRow dr in dt.Rows)
            {

                TABLE_NAME = PropCaseStartLower(dr["TABLE_NAME"].ToString()).Replace("_", "");

                pkTable = GetPK(dr["TABLE_NAME"].ToString());

                cmdTxt = "select data_type from INFORMATION_SCHEMA.COLUMNS where table_name = '" + dr["TABLE_NAME"].ToString() + "' and ordinal_position = 1";

                SqlCommand cmdType = new SqlCommand();
                cmdType.CommandText = cmdTxt;
                cmdType.Connection = conn;

                SqlDataAdapter odaColl = new SqlDataAdapter(cmdType);
                DataTable dtColl = new DataTable();
                odaColl.Fill(dtColl);

                keyType = DecodeType(dtColl.Rows[0][0].ToString());
                keyDbType = DecodeTypeForDB(dtColl.Rows[0][0].ToString());

                cmdTxt = @"SELECT 
					TABLE_CATALOG,
					TABLE_SCHEMA, 
					TABLE_NAME, 
					COLUMN_NAME, 
					ORDINAL_POSITION, 
					COLUMN_DEFAULT AS DefaultSetting, 
					IS_NULLABLE,
					DATA_TYPE, 
					CHARACTER_MAXIMUM_LENGTH AS MaxLength, 
					DATETIME_PRECISION AS DatePrecision,
					COLUMNPROPERTY(object_id('[' + TABLE_SCHEMA + '].[' + TABLE_NAME + ']'), COLUMN_NAME, 'IsIdentity') AS IsIdentity,
					COLUMNPROPERTY(object_id('[' + TABLE_SCHEMA + '].[' + TABLE_NAME + ']'), COLUMN_NAME, 'IsComputed') as IsComputed
				FROM  INFORMATION_SCHEMA.COLUMNS
				WHERE TABLE_NAME= '" + dr["TABLE_NAME"].ToString() + "' ORDER BY Ordinal_Position";


                SqlCommand cmdColl = new SqlCommand();
                cmdColl.CommandText = cmdTxt;
                cmdColl.Connection = conn;
                cmdColl.Dispose();

                odaColl = new SqlDataAdapter(cmdColl);
                dtColl = new DataTable();
                odaColl.Fill(dtColl);

                collName = string.Empty;
                destColl = string.Empty;
                parColl = string.Empty;
                dbType = string.Empty;
                isNull = string.Empty;
                updCollPar = string.Empty;
                collTemp = string.Empty;
                parTemp = string.Empty;

                insColl = string.Empty;
                insCollPar = string.Empty;
                paramColl = string.Empty;
                clearColl = string.Empty;
                htmlTextBox = string.Empty;

                foreach (DataRow drColl in dtColl.Rows)
                {
                    collTemp = drColl["COLUMN_NAME"].ToString();
                    parTemp = PropCaseStartLower(drColl["COLUMN_NAME"].ToString()).Replace("_", "");

                    destColl = destColl + ", " + collTemp;
                    parColl = parColl + ", @" + parTemp;

                    int isIdentity = (drColl["IsIdentity"] == null ? 0 : Convert.ToInt32(drColl["IsIdentity"]));

                    if (!(collTemp.ToLower().Contains("create")) && !(collTemp.ToLower().Contains("password")) && isIdentity != 1)
                    {
                        updCollPar = updCollPar + ", " + collTemp + " = @" + parTemp + "\n";
                    }

                    if (isIdentity == 0 && !(collTemp.ToLower().Contains("modified")) && !(collTemp.ToLower().Contains("created")))
                    {
                       

                        htmlTextBox = htmlTextBox + "\t\t\t\t\t\t\t txt" + PropCaseStartLower(collTemp).Replace("_", "")+".Text = obj."+PropCaseStartLower(collTemp).Replace("_", "")+".ToString().Trim();\n";

                    }

                }
            }

            sql.AppendLine("using System;");
            sql.AppendLine("using System.Collections.Generic;");
            sql.AppendLine("using System.Linq;");
            sql.AppendLine("using System.Web;");
            sql.AppendLine("using System.Web.UI;");
            sql.AppendLine("using System.Web.UI.WebControls;");
            sql.AppendLine("using " + NameSpace+";");
            sql.AppendLine("using " + NameSpace+";");
            sql.AppendLine("using System.Configuration;");
            sql.AppendLine("\n");
            sql.AppendLine("namespace " + NameSpace);
            sql.AppendLine("\t{");
            sql.AppendLine("\t\tpublic partial class " + PropCaseStartLower(mTableName).Replace("_", "") + "_Form" + ": BasePage");
            sql.AppendLine("\t\t{");
            sql.AppendLine("\t\t\tprivate int id = -1;");
            sql.AppendLine("\t\t\tprivate " + PropCaseStartLower(mTableName).Replace("_", "") + "Repo " + PropCaseStartLower(mTableName).Replace("_", "") + "Dal = new " + PropCaseStartLower(mTableName).Replace("_", "") + "Repo(ConfigurationManager.ConnectionStrings["+"\""+"Rtgs"+"\""+"].ConnectionString);");
            sql.AppendLine("\n");
            sql.AppendLine("\t\t\tprotected void Page_Load(object sender, EventArgs e)");
            sql.AppendLine("\t\t\t{");
            sql.AppendLine("\t\t\t\tif (Request.Params["+"\""+"Id"+"\""+"] != null)");
            sql.AppendLine("\t\t\t\t{");
            sql.AppendLine("\t\t\t\t\tid = Convert.ToInt32(Request.Params[" + "\"" + "Id" + "\"" + "]);");
            sql.AppendLine("\t\t\t\t}");
            sql.AppendLine("\n");
            sql.AppendLine("\t\t\tif (!Page.IsPostBack)");
            sql.AppendLine("\t\t\t{");
            sql.AppendLine("\t\t\t\tif (id > 0) // Edit an existing item");
            sql.AppendLine("\t\t\t\t\t{");
            sql.AppendLine("\t\t\t\t\t\t" + PropCaseStartLower(mTableName).Replace("_", "") + " obj = " + PropCaseStartLower(mTableName).Replace("_", "") + "Dal.GetById(id);");
            sql.AppendLine("\t\t\t\t\t\tif (obj != null)");
            sql.AppendLine("\t\t\t\t\t\t{");
            sql.AppendLine("\t\t\t\t\t\t\t hfID.Value = id.ToString().Trim();");
            sql.AppendLine(htmlTextBox);
            sql.AppendLine("\t\t\t\t\t\t}");
            sql.AppendLine("\t\t\t\t\t}");
            sql.AppendLine("\t\t\t\t}");
            sql.AppendLine("\t\t\t}");
            sql.AppendLine("\t\t}");
            sql.AppendLine("\t}");

            return sql.ToString();
        }
    }
}
